﻿Imports System.Data.SqlClient

Public Class frmRSaldosInfonavit
    Dim Infonavit(1500) As Integer
    Private Sub frmRSaldosInfonavit_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        txtBimestre.Text = 0
        txtAnio.Text = Now.Year

    End Sub
    Sub BuscaInfonavit()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "SELECT IdEmpleado, sum(Monto) as Total " & _
                 " FROM AbonosInfonavit " & _
                 " WHERE Bimestre=" & txtBimestre.Text & " AND Anio=" & txtAnio.Text & " " & _
                 " GROUP BY IdEmpleado"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim IDemp As Integer

        Do While rdBuscar.Read()
            IDemp = rdBuscar("IdEmpleado")
            Infonavit(IDemp) = rdBuscar("Total")
        Loop

    End Sub
    Sub Reporte()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        
        Dim strSql As String = ""

        strSql = "SELECT idempleado, codigo, nombrecompleto"
        strSql = strSql & " from Empleados"
        strSql = strSql & " where EstadoEmpleado<>'B'"
        strSql = strSql & " order by NombreCompleto"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 1).value = "Codigo"
        wb.SHEETS(1).cells(rng, 2).value = "Nombre del Empleado"
        wb.SHEETS(1).cells(rng, 3).value = "Cargo"
        wb.SHEETS(1).cells(rng, 4).value = "Abono"
        wb.SHEETS(1).cells(rng, 5).value = "Saldo"

        Dim i As Integer
        For i = 1 To 5
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).interior.colorindex = 1
            wb.SHEETS(1).cells(rng, i).font.colorindex = 2
        Next

        rng = rng + 1

        Dim Cargo As Double = 0
        Dim IdEmpleado As Integer = 0
        Dim Abono As Double
        Dim Saldo As Double = 0

        Do While rdBuscar.Read()
            IdEmpleado = rdBuscar("idempleado")
            Cargo = BuscarDblDatoCitra("SELECT SUM(Monto) AS Monto FROM CargosInfonavit WHERE IdEmpleado=" & IdEmpleado & " AND Bimestre=" & txtBimestre.Text & " AND YEAR(Fecha)=" & txtAnio.Text)
            If Cargo > 0 Then
                wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Codigo").ToString.Trim
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Nombrecompleto").ToString.Trim
                wb.SHEETS(1).cells(rng, 3).value = Cargo
                Abono = Infonavit(IdEmpleado)
                wb.SHEETS(1).cells(rng, 4).value = Abono
                Saldo = Cargo - Abono
                wb.SHEETS(1).cells(rng, 5).value = Saldo
                For i = 1 To 5
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                Next
                rng = rng + 1
                Abono = 0
                Cargo = 0
                Saldo = 0
            End If
        Loop

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 15
        wb.SHEETS(1).columns(3).numberformat = "#,##0.00"
        wb.SHEETS(1).columns(4).numberformat = "#,##0.00"
        wb.SHEETS(1).columns(5).numberformat = "#,##0.00"

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Saldos de Infonavit "
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs)


    End Sub

    Private Sub ToolStripButton2_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton2.Click
        BuscaInfonavit()

        Reporte()
    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub
End Class